#!/usr/bin/python
# -*- coding: UTF-8 -*-
from proxyips.mysqlmoudel.myusesql.basesql import baseSql


class SpiderSql(baseSql):
    """
    sql管理层
    """

    def __init__(self):
        pass

    def savecommentitem(self, tablename,items):
        print (items)
        bool,sql = self.insert_more_string(items)
        print (sql)
        if bool:
            sql = "REPLACE INTO "+tablename+" "+sql
            print sql
        else:
            assert "sql err,please check,at spidersql file and savecommentitem method,insert_more_string method"
        
        return sql.decode("utf-8")

    def getproxyip(self):
        return "SELECT id,ip,port FROM mws_proxy_ips WHERE `work`='1' ORDER BY usetimes ASC LIMIT 0,1;".decode("utf-8")

    def updateproxyipusetime(self,id):
        sql = "update mws_proxy_ips set usetimes = usetimes+1 WHERE `id` = %s" % id
        return sql.decode("utf-8")

    def validationproxyip(self):
        sql = "SELECT id,ip,`port` FROM `mws_proxy_ips` WHERE work=1 AND source='1' AND (baiducanuse is NULL or amanzoncanuse is NULL) limit 0,500"
        return sql.decode("utf-8")

    def lockproxyip(self, status, id):
        sql = "UPDATE `mws_proxy_ips` SET baiducanuse = %s,amanzoncanuse = %s WHERE id = %d" % ('\''+status+'\'','\''+status+'\'', id)
        # print sql
        return sql.decode("utf-8")
    
    def getproxyipstatus(self, status):
        sql = "SELECT id,ip,`port` FROM `mws_proxy_ips` WHERE baiducanuse = %s or amanzoncanuse = %s" % ('\''+status+'\'','\''+status+'\'')
        # print sql
        return sql.decode("utf-8")
    
    def setbaiducanuse(self,status,id):
        sql = "UPDATE `mws_proxy_ips` SET baiducanuse= %s WHERE id = %s" % ('\''+str(status)+'\'', str(id))
        return sql.decode("utf-8")

    def setamazoncanuse(self, status, id):
        sql = "UPDATE `mws_proxy_ips` SET amanzoncanuse= %s WHERE id = %s" % ('\''+str(status)+'\'', str(id))
        return sql.decode("utf-8")

    def getproxyipspider(self):
        return "SELECT id,ip,port FROM mws_proxy_ips WHERE `work`='1' AND baiducanuse='1' ORDER BY usetimes ASC LIMIT 0,1;".decode("utf-8")

    def deleteineffectualips(self):
        return "DELETE FROM `mws_proxy_ips` WHERE baiducanuse='0' AND amanzoncanuse='0'".decode("utf-8")
